# -*- coding: utf-8 -*-

#【案例10-1】输出到Excel文件
import pandas as pd
df = pd.read_csv("D:/DataAnalysis/Chapter10Data/BJPM10.csv")
df.to_excel("D:/DataAnalysis/Chapter10Data/BJPM10.xlsx")

#指定输出的工作表名称为BJPM10
import pandas as pd
df = pd.read_csv("D:/DataAnalysis/Chapter10Data/BJPM10.csv")
df.to_excel("D:/DataAnalysis/Chapter10Data/BJPM10.xlsx", sheet_name = "BJPM10")

#不输出索引列
import pandas as pd
df = pd.read_csv("D:/DataAnalysis/Chapter10Data/BJPM10.csv")
df.to_excel("D:/DataAnalysis/Chapter10Data/BJPM10.xlsx", 
            sheet_name = "BJPM10", index = False)

#指定输出的列名
import pandas as pd
df = pd.read_csv("D:/DataAnalysis/Chapter10Data/BJPM10.csv")
df.to_excel("D:/DataAnalysis/Chapter10Data/BJPM10.xlsx", 
            sheet_name = "BJPM10", index = False,
            columns = ["date", "hour", "官园", "奥体中心"])

#替代缺失值
import pandas as pd
df = pd.read_csv("D:/DataAnalysis/Chapter10Data/BJPM10.csv")
df.to_excel("D:/DataAnalysis/Chapter10Data/BJPM10.xlsx", 
            sheet_name = "BJPM10", index = False,
            columns = ["date", "hour", "官园", "奥体中心"], na_rep = 0)


#【案例10-2】输出到CSV文件
import pandas as pd
df = pd.read_excel("D:/DataAnalysis/Chapter10Data/Insurance.xlsx")
df.to_csv("D:/DataAnalysis/Chapter10Data/Insurance.csv")

#设定输出编码格式
import pandas as pd
df = pd.read_excel("D:/DataAnalysis/Chapter10Data/Insurance.xlsx")
df.to_csv("D:/DataAnalysis/Chapter10Data/Insurance.csv", encoding = "utf-8-sig")

#去除索引列
import pandas as pd
df = pd.read_excel("D:/DataAnalysis/Chapter10Data/Insurance.xlsx")
df.to_csv("D:/DataAnalysis/Chapter10Data/Insurance.csv", 
          encoding = "utf-8-sig", index = False)

#指定分隔符
import pandas as pd
df = pd.read_excel("D:/DataAnalysis/Chapter10Data/Insurance.xlsx")
df.to_csv("D:/DataAnalysis/Chapter10Data/Insurance.csv", 
          encoding = "utf-8-sig", index = False, sep = ";")

#指定输出的列名
import pandas as pd
df = pd.read_excel("D:/DataAnalysis/Chapter10Data/Insurance.xlsx")
df.to_csv("D:/DataAnalysis/Chapter10Data/Insurance.csv", 
          encoding = "utf-8-sig", index = False, sep = ";",
          columns = ["手机号", "性别", "工作年限"])


#【案例10-3】输出到Text文本文件
import pandas as pd
df = pd.read_excel("D:/DataAnalysis/Chapter10Data/Insurance.xlsx")
df.to_csv("D:/DataAnalysis/Chapter10Data/Insurance.txt")

#指定分隔符
import pandas as pd
df = pd.read_excel("D:/DataAnalysis/Chapter10Data/Insurance.xlsx")
df.to_csv("D:/DataAnalysis/Chapter10Data/Insurance.txt", sep = ";")

#去除索引列
import pandas as pd
df = pd.read_excel("D:/DataAnalysis/Chapter10Data/Insurance.xlsx")
df.to_csv("D:/DataAnalysis/Chapter10Data/Insurance.txt", 
          sep = ";", index = False)

#指定输出的列名
import pandas as pd
df = pd.read_excel("D:/DataAnalysis/Chapter10Data/Insurance.xlsx")
df.to_csv("D:/DataAnalysis/Chapter10Data/Insurance.txt", 
          sep = ";", index = False, columns = ["手机号", "性别", "工作年限"])


#【案例10-4】保存至SQLServer数据库
import pandas as pd
df = pd.read_excel("D:/DataAnalysis/Chapter10Data/Insurance.xlsx")
from sqlalchemy import create_engine
#dialect：mssql
#driver：pymssql
#username：sa
#password：123
#host：localhost
#database：DataAnalysisBook
#创建数据库连接
conn = create_engine("mssql+pymssql://sa:123@localhost/DataAnalysisBook")
df.to_sql("Insurance", conn, index = False)
#断开数据库连接
conn.dispose()


#【案例10-5】保存至MySQL数据库
import pandas as pd
#读入Excel数据
df = pd.read_excel("D:/DataAnalysis/Chapter10Data/Insurance.xlsx")
from sqlalchemy import create_engine
import pandas as pd
#dialect：mysql
#driver：pymysql
#username：root
#password：123
#host：localhost
#database：DataAnalysisBook
#创建数据库连接
conn = create_engine("mysql+pymysql://root:123@localhost/DataAnalysisBook")
#输出到数据库
df.to_sql("Insurance", conn, index = False)
#断开数据库连接
conn.dispose()


#【案例10-6】输出到MongoDB数据库
import pandas as pd
#读入Excel数据
df = pd.read_excel("D:/DataAnalysis/Chapter10Data/Insurance.xlsx")
import pymongo
client = pymongo.MongoClient(host='localhost', port=27017) 
mydb = client["DataAnalysisBook"]
import json
mydata = mydb["Insurance"]
mydata.collection.insert_many(json.loads(df.T.to_json()).values())


#【案例10-7】2010年至2019年GDP数据输出
import pandas as pd
df = pd.read_excel("D:/DataAnalysis/Chapter10Data/GDP.xlsx")
import numpy as np
#获取2015年至2019年第一产业、第二产业、第三产业增加值数据
df_save = df.iloc[2:5,np.r_[6:]]
#存入GDP.csv文件
df_save.to_csv("D:/DataAnalysis/Chapter10Data/GDP.csv", 
          encoding = "utf-8-sig", index = False, sep = ";")
from sqlalchemy import create_engine
#dialect：mysql
#driver：pymysql
#username：root
#password：123
#host：localhost
#database：DataAnalysisBook
#创建数据库连接
conn = create_engine("mysql+pymysql://root:123@localhost/DataAnalysisBook")
#输出到数据库
df_save.to_sql("GDP", conn, index = False)
#断开数据库连接
conn.dispose()
